﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections;
using System.Data;
using DomainModel;

namespace DAL.Service.Impl
{
    public class UserInfoServiceImpl : BaseServiceImpl, IUserInfoService
    {
        public IList getUserInfoByCard(string nick, string name, string card)
        {
            MyQuery query = new MyQuery();
            query.setQueryString(@"select top 50 * from (select distinct u_id,u_nick,u_name,u_enName from joy.UserInfo as a
            inner join joy.UserCardCost as b on a.u_id=b.c_uid
            inner join joy.UserCard as c on b.c_ucid=c.uc_id
            where u_nick like '%'+@nick+'%' and u_name like '%'+@name+'%' and uc_card like '%'+@card+'%')d");
            query.addParas("@nick", nick);
            query.addParas("@name", name);
            query.addParas("@card", card);
            return base.getList(query);
       }
        public IList getUserInfolist(string nick,string name,string enname)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select top 100 u_id,u_nick,u_name,u_enName from joy.UserInfo where u_nick like '%'+@nick+'%' and u_name like '%'+@name+'%' and u_enName like '%'+@enname+'%'");
            query.addParas("@nick", nick);
            query.addParas("@name", name);
            query.addParas("@enname", enname);
            return base.getList(query);
        }

        public IList getUserInfo(string uname, string upass)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select * from joy.UserInfo where u_nick=@uname and u_pass=@upass");
            query.addParas("@uname", uname);
            query.addParas("@upass", upass);
            return base.getList(query);
        }

        public IList regNick(string nick,string uid)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select * from joy.UserInfo where u_nick = @nick and u_id !=@uid");
            query.addParas("@nick", nick);
            query.addParas("@uid", uid);
            return base.getList(query);
        }

        public IList regNumber(string number,string uid)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select * from joy.UserInfo where u_number=@number and u_id !=@uid");
            query.addParas("@number", number);
            query.addParas("@uid", uid);
            return base.getList(query);
        }
        public IList getuserinfobyid(int uid)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select * from joy.UserInfo where u_id=@uid");
            query.addParas("@uid", uid);
            return base.getList(query);
        }
        public IList getUserinfoListSearch(string uname, string unick, string unumber, string ujwclass, string uteacher1, string ucard, string uvip, string ustate, string uregistTime_s, string uregistTime_e, int pageno, int pagesize, string orderName, string orderEname, string orderNumer, string orderBrithday, string orderVip, string orderJwclass, string orderId)
        {
            MyQuery query = new MyQuery();
            StringBuilder sb = new StringBuilder();
            string _sql = "";
            string _order = "";
            #region order
            if (orderName=="true")
            {
                _order += "u_name desc,";
            }
            if (orderEname=="true")
            {
                _order += " u_enName desc,";
            }
            if (orderNumer=="true")
            {
                _order += " u_number desc,";
            }
            if (orderBrithday=="true")
            {
                _order += " u_birthday desc,";
            }
            if (orderVip == "true")
            {
                _order += " u_vip desc,";
            }
            if (orderJwclass=="true")
            {
                _order += " u_jwclass desc,";
            }
            if (orderId == "true" || _order=="")
            {
                _order += " u_id desc,";
            }
            _order = _order.Substring(0, _order.Length - 1);
            #endregion

            if (!String.IsNullOrEmpty(uname))
            {
                _sql += " and u_name like '%" + uname + "%'";
            }
            if (!String.IsNullOrEmpty(unick))
            {
                _sql += " and u_nick like '%" + unick + "%'";
            }
            if (!String.IsNullOrEmpty(unumber))
            {
                _sql += " and u_number like '%" + unumber + "%'";
            }
            if (ujwclass!="-1")
            {
                _sql += " and u_jwclass =@ujwclass";
                query.addParas("@ujwclass", ujwclass);
            }
            if (!String.IsNullOrEmpty(uteacher1))
            {
                _sql += " and u_teacher1 like '%" + uteacher1 + "%'";
            }
            if (!String.IsNullOrEmpty(ucard))
            {
                _sql += " and uc_card like '%" + ucard + "%'";
            }
            if (uvip != "-1")
            {
                _sql += " and u_vip =@uvip";
                query.addParas("@uvip", uvip);
            }
            if (ustate!="-1")
            {
                _sql += " and u_state =@ustate";
                query.addParas("@ustate", ustate);
            }
            if (!String.IsNullOrEmpty(uregistTime_s))
            {
                _sql += " and u_time >= @uregistTime_s";
                query.addParas("@uregistTime_s", uregistTime_s);
            }
            if (!String.IsNullOrEmpty(uregistTime_e))
            {
                uregistTime_e = uregistTime_e + " 23:59:59";
                _sql += " and u_time < @uregistTime_e";
                query.addParas("@uregistTime_e", uregistTime_e);
            }
            if (pageno == 1)
            {
                sb.Append("SELECT count(u_id) as total from joy.userinfo where u_id in (select u_id from joy.userinfo a left join joy.UserCardCost b on a.u_id=b.c_uid left join joy.UserCard c on b.c_ucid = c.uc_id where 1=1 " + _sql + " group by u_id);");
                sb.Append("select top " + pagesize + " *  from joy.userinfo where u_id in (select u_id from joy.userinfo a left join joy.UserCardCost b on a.u_id=b.c_uid left join joy.UserCard c on b.c_ucid = c.uc_id where 1=1 " + _sql + " group by u_id) order by " + _order + ";");
            }
            else
            {
                sb.Append("select * from(");
                sb.Append("select *,ROW_NUMBER() OVER (order by " + _order + ") as rno from joy.userinfo where u_id in (select u_id from joy.userinfo a left join joy.UserCardCost b on a.u_id=b.c_uid left join joy.UserCard c on b.c_ucid = c.uc_id where 1=1  " + _sql + " group by u_id)");
                sb.Append(") as a where a.rno>@start and a.rno<@end  order by " + _order + ";");
                query.PageNo = pageno;
                query.PageSize = pagesize; ;
            }
            query.setQueryString(sb.ToString());
            return base.getList(query);
        }

        public int updatepass(int uid)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("update joy.UserInfo set u_pass='49BA59ABBE56E057' WHERE u_id =@uid");
            query.addParas("@uid", uid);
            return base.exeUpdateSql(query);
        }
        public int cuvip(string uid, string uvip)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("update joy.UserInfo set u_vip=@uvip,u_vipUpdateTime=getdate() WHERE u_id =@uid");
            query.addParas("@uid", uid);
            query.addParas("@uvip", uvip);
            return base.exeUpdateSql(query);
        }
        public void changepwd(string uid, string pwd)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("update joy.UserInfo set u_pass=@pwd WHERE u_id =@uid");
            query.addParas("@uid", uid);
            query.addParas("@pwd", pwd);
            base.exeUpdateSql(query);
        }
        public IList getUserCardList(int uid)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select case when c_times = 0 and c_endtime>getdate() and uc_state=1 then 'true' when c_times>usedTimes and c_endtime>getdate() and uc_state=1 then 'true' else 'false' end zxflag,* from (select c_times,c_endtime,uc_id,uc_card,uc_chargemode,uc_state,c_starttime,uc_kf,uc_yj,uc_bz,joy.getCardTimes(c_ucid) usedTimes,uctf_fy,uctf_yj,uctf_bz from joy.UserCardCost a inner join joy.UserCard b on a.c_ucid=b.uc_id left join joy.UserCardTF c on b.uc_id = c.uctf_ucid where c_uid =@uid )a order by c_starttime asc");
            query.addParas("@uid", uid);
            return base.getList(query);
        }

        public IList userBooksList(int uid)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("SELECT lbd_isbn,lb_bookName,lb_translatedName,lb_state,lb_forAge,lb_level,lb_bookType,lbi_time,joy.getBookReturnTime(lbi_id) returntime,lbi_appraisal FROM joy.LibraryBookIndent a INNER JOIN joy.LibraryBookIndentDetail b ON a.lbi_id = b.lbid_lbiid INNER JOIN joy.LibraryBookDetail c ON b.lbid_lbdid=c.lbd_id INNER JOIN joy.LibraryBook d ON c.lbd_lbid = d.lb_id WHERE lbi_uid = @uid order by lbi_time desc,lbi_id asc");
            query.addParas("@uid", uid);
            return base.getList(query);
        }
        public DataTable userBooksListTable(int uid)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("SELECT lbd_isbn,lb_bookName,lb_translatedName,lb_state,lb_forAge,lb_level,lb_bookType,lbi_time,joy.getBookReturnTime(lbi_id) returntime, lbi_appraisal FROM joy.LibraryBookIndent a INNER JOIN joy.LibraryBookIndentDetail b ON a.lbi_id = b.lbid_lbiid INNER JOIN joy.LibraryBookDetail c ON b.lbid_lbdid=c.lbd_id INNER JOIN joy.LibraryBook d ON c.lbd_lbid = d.lb_id WHERE lbi_uid = @uid order by lbi_time desc,lbi_id asc");
            query.addParas("@uid", uid);
            return base.getDataTable(query);
        }

        public int getUserSaveCount(int bookid,int uid)
        {
            MyQuery query = new MyQuery();
            query.setQueryString("select count(*) as total from joy.UserSave as a inner join joy.DirectoryList as b on a.us_dlid=b.dl_id where us_lbid=@bookid and dl_uid=@uid");
            query.addParas("@bookid", bookid);
            query.addParas("@uid", uid);
           IList list= base.getList(query);
           return int.Parse(((Hashtable)list[0])["total"].ToString());
        }
    }
}
